package com.miao.util.poi;

import com.miao.util.basic.DataDealUtil;
import com.miao.util.date.BatchDateTimeUtil;
import com.miao.util.file.FileUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.awt.*;
import java.io.*;
import java.util.Random;


public class POIUtil {

    public static final int NONMERGE = 0;
    public static final int FIRSTMERGE = 2;
    public static final int INMERGE_BUT_NOFIRST = 1;
    private static Logger logger = Logger.getLogger(POIUtil.class);

    //-----------------------------EXCEL2003,EXCEL2007文件读写------------------------------------------------

    /**
     * 判断文件扩展名,是否是EXCEL文件
     */
    public static boolean isExcel(String fileName) {
        if (StringUtils.isBlank(fileName))
            return false;
        fileName = fileName.trim().toLowerCase();
        if (fileName.endsWith(".xls"))
            return true;
        else if (fileName.endsWith(".xlsx"))
            return true;
        else if (fileName.toLowerCase().endsWith(".csv"))
            return true;
        else
            return false;
    }

    /**
     * 判断文件扩展名,是否是TXT文件
     */
    public static boolean isTxt(String fileName) {
        if (StringUtils.isBlank(fileName))
            return false;
        fileName = fileName.trim().toLowerCase();
        if (fileName.endsWith(".txt"))
            return true;
        else
            return false;
    }

    /**
     * 判断文件扩展名,是否是sql文件
     */
    public static boolean isSql(String fileName) {
        if (StringUtils.isBlank(fileName))
            return false;
        fileName = fileName.trim().toLowerCase();
        if (fileName.endsWith(".sql"))
            return true;
        else
            return false;
    }

    /**
     * 判断文件扩展名,只要不是.xlsx结尾,都默认为2003
     */
    public static boolean is2003(String fileName) {
        if (StringUtils.isBlank(fileName))
            return true;
        fileName = fileName.trim().toLowerCase();
        if (fileName.endsWith(".xls"))
            return true;
        else if (fileName.endsWith(".xlsx"))
            return false;
        else
            return true;
    }

    /**
     * creates an {@link HSSFWorkbook} the specified OS filename.
     *
     * @throws IOException
     * @throws FileNotFoundException
     * @throws Exception
     */
    private static HSSFWorkbook read2003File(String fileName) throws FileNotFoundException, IOException, Exception {
        //FileOperateUtil.isFileExist(fileName);
        InputStream is = new FileInputStream(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        is.close();
        return workbook;
    }

    /**
     * creates an {@link HSSFWorkbook} the specified OS filename.
     */
    private static XSSFWorkbook read2007File(String fileName) throws IOException {
        InputStream is = new FileInputStream(fileName);
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        is.close();
        logger.debug("注意![" + fileName + "]打开的是EXCEL2007格式文件!生成的是XSSFWorkbook对象,此对象不支持跨文件公式关联");
        return workbook;
    }

    /**
     * 读EXCEL,返回Workbook对象
     * 支持xls,xlsx两种格式
     *
     * @param pathString
     * @param fileName
     * @return
     * @throws IOException
     * @throws Exception
     */
    public static Workbook readExcelFile(String pathString, String fileName) throws FileNotFoundException, Exception, IOException {
        if (StringUtils.isBlank(fileName)) {
            logger.error("打开excel文件失败!原因:文件名为空!");
            throw new Exception("fileName is null");
        }

        //fileName = fileName.trim().toLowerCase();
        String fullPath = FileUtil.addPathSuffix(pathString)
                + FileUtil.dropPathPrefix(fileName);
        if (fullPath.endsWith("xls") || fullPath.endsWith("XLS"))
            return read2003File(fullPath);
        else if (fullPath.endsWith("xlsx") || fullPath.endsWith("XLSX"))
            return read2007File(fullPath);
        else {
            logger.error("文件" + fullPath + "扩展名不是xls或xlsx");
            throw new Exception("文件" + fullPath + "扩展名不是xls或xlsx");
        }
    }

    /**
     * creates an {@link HSSFWorkbook} the specified OS filename.
     *
     * @throws IOException
     */
    private synchronized static void writeWBFile(Workbook wb, String fileName) throws IOException, IllegalArgumentException {
        //logger.debug("准备存盘");
        // create a new file
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(fileName);
            // write the workbook to the output stream
            wb.write(out);

            //logger.debug("存盘完毕");
        } catch (IOException e) {
            throw e;
        } finally {
            // close our file (don't blow out our file handles
            if (out != null) {
                try {
                    out.close();
                } catch (RuntimeException e) {
                    logger.error("EXCEL文件写打开,关闭时出异常");
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 写EXCEL,
     *
     * @param wb
     * @param pathString
     * @param fileName   注意,不要带扩展名
     * @throws IOException
     * @throws Exception
     */
    public static void writeExcelFile(Workbook wb, String pathString, String fileName) throws IOException, Exception, IllegalArgumentException {
        if (wb == null)
            throw new Exception("Excel obj is null");
        if (StringUtils.isBlank(fileName))
            throw new Exception("fileName is null");

        // 取不带扩展名的文件名
        //fileName = fileName.trim().toLowerCase();
        if (fileName.contains(".")) {
            fileName = fileName.substring(0, fileName.lastIndexOf("."));
        }
        String fullPath = FileUtil.addPathSuffix(pathString)
                + FileUtil.dropPathPrefix(fileName);
        //logger.debug(fullPath);
        if (wb instanceof HSSFWorkbook)
            writeWBFile(wb, fullPath + ".xls");
        else if (wb instanceof XSSFWorkbook)
            writeWBFile(wb, fullPath + ".xlsx");
        else
            throw new Exception("Object is not excel object");
    }

    //----------------------------------新建------------------------------------------------//

    /**
     * 在workboot某个位置建立一个新的sheet,并且搭一个空CELL架子
     * 缺省单元格类型是3(BLANK)
     */
    public static String creatSheet(Workbook wb, String sheetName, Integer order,
                                    Integer maxRows, Integer maxCells) {
        if (wb == null || StringUtils.isBlank(sheetName)
                || order < 0 || order > wb.getNumberOfSheets()
                || maxRows < 0 || maxCells < 0)
            return null;

        // 先检查 sheetName,避免出现名称冲突,有重名的,就在名称后加二位随机数
        sheetName = getNewSheetName(wb, sheetName);

        // 建sheet
        wb.createSheet(sheetName);
        wb.setSheetOrder(sheetName, order);

        // 建 X * Y的空CELL架子
        Sheet sheet1 = wb.getSheet(sheetName);
        for (int i = 0; i < maxRows; i++) {
            Row row = sheet1.createRow(i);
            for (int j = 0; j < maxCells; j++) {
                row.createCell(j);
                // 缺省类型是3(BLANK)
            }
        }

        // 要返回sheet名,因为sheet名称可能因为重名而不太一致.
        return sheetName;
    }

    /**
     * 检查存在null的cell就建立新CELL和ROW
     *
     * @param sheet1
     * @param maxRows
     * @param maxCols
     * @param evaluator
     * @return
     */
    public static boolean checkNullCell(Sheet sheet1, int maxRows, int maxCols, FormulaEvaluator evaluator) {

        logger.debug("检查null空格,并创建");
        boolean modifyFlag = false;

        for (int rowNum = 0; rowNum < maxRows; rowNum++) {
            Row row = sheet1.getRow(rowNum);

            if (row != null) {
                for (int colNum = 0; colNum < maxCols; colNum++) {
                    Cell cell = POIUtil.getCell(sheet1, rowNum, colNum);
                    // cell为空时,建新CELL
                    if (null == cell) {
                        modifyFlag = true;
                        //建新CELL
                        row.createCell(colNum);
                        ;
                        logger.debug("resize:建新列,NO." + rowNum + "行,NO." + colNum + "列");
                    }
                } // end of for
            }
            // 行为空时,就要建新行
            else {
                modifyFlag = true;
                row = sheet1.createRow(rowNum);
                logger.debug("resize:建新行,NO." + rowNum + "行,并自动建" + maxCols + "列");
                for (int colNum = 0; colNum < maxCols; colNum++) {
                    //建新CELL
                    row.createCell(colNum);
                }
            }

        }

        return modifyFlag;  // 提醒调用者进行存盘,和同步.
    }

    public static void printExcel(Workbook wb, FormulaEvaluator evaluator, Integer sheetNum) {
        Sheet sheet1 = wb.getSheetAt(sheetNum);

        System.out.println("=========================" + wb.getSheetName(sheetNum) + "=================================");

        int maxRows = getSheetRowsNum(wb, POIUtil.getSheetName(wb, 0));
        int maxCols = getSheetColumnsNum(wb, POIUtil.getSheetName(wb, 0));
        System.out.println("最大行数(1BASE):" + maxRows + "|" + "最大列数(1BASE):" + AxisFormulaTrans.getValue(maxCols - 1));

        System.out.println("-----------形式:,[坐标]值[类型 0数字,1字符,2,公式,3空]------------------------------------------------");

        for (int rowNum = 0; rowNum < maxRows; rowNum++) {
            System.out.print("no." + rowNum + "--");
            for (int colNum = 0; colNum < maxCols; colNum++) {
                Cell cell = getCell(sheet1, rowNum, colNum);
                if (null != cell) {
                    try {
                        System.out.print(",[" + AxisFormulaTrans.getCellFormatStr(cell) + "]" +
                                getCellValue(cell, evaluator, null) +
                                "[" + cell.getCellType() + "]");
                    } catch (Exception e) {
                        System.out.println(e.getMessage());
                    }
                }
            }
            System.out.println("");
        }

        System.out.println("-----------------------------------------------------------");
        int mrNums = sheet1.getNumMergedRegions();
        System.out.println("合并单元格数:" + mrNums);
        for (int i = 0; i < mrNums; i++) {
            CellRangeAddress cra = sheet1.getMergedRegion(i);
            System.out.println("no." + i + ":" + AxisFormulaTrans.getCellPos(cra.getFirstRow(), cra.getFirstColumn())
                    + " to " + AxisFormulaTrans.getCellPos(cra.getLastRow(), cra.getLastColumn()));
        }
        System.out.println("============================打印结束==============================");

    }

    //--------------------------------------SHEET操作-----------------------------------//

    /**
     * 获取一个HSSFWorkbook对象里的所有SHEET的名字
     * 返回SHEET名字数组
     *
     * @param wb
     * @return
     */
    public static String[] getSheetNames(Workbook wb) {
        if (wb == null)
            return null;
        int sheetNum = wb.getNumberOfSheets();
        if (sheetNum == 0)
            return null;
        String[] sheetNames = new String[sheetNum];
        for (int i = 0; i < sheetNum; i++)
            sheetNames[i] = wb.getSheetName(i);
        return sheetNames;
    }

    /**
     * 返回一个SHEET的INDEX
     *
     * @param wb
     * @param sheetName
     * @return 参数判空失败的情况下, 返回-1
     * 不存在该SHEET,返回-1
     * @author guanxiaoyu
     */
    public static int getSheetNum(Workbook wb, String sheetName) {
        if ((wb == null) || (StringUtils.isBlank(sheetName)))
            return -1;
        if (wb.getSheet(sheetName) == null)
            return -1;
        else
            return wb.getSheetIndex(sheetName);
    }

    /**
     * 返回一个SHEET NO的NAME
     *
     * @param wb
     * @return 返回sheet名, 判空失败返回null
     * 不存在该SHEET返回null
     * @author guanxiaoyu
     */
    public static String getSheetName(Workbook wb, int sheetIndex) {
        if ((wb == null) || (sheetIndex < 0))
            return null;
        if (wb.getSheetAt(sheetIndex) == null)
            return null;
        else
            return wb.getSheetName(sheetIndex);
    }

    /**
     * 从一个WB中获取一个不重复的sheetName,如果重复,就在名称后加随机数
     *
     * @param wb
     * @param sheetName
     * @return
     */
    public static String getNewSheetName(Workbook wb, String sheetName) {
        if (wb.getSheet(sheetName) != null) {
            Random ran = new Random();
            sheetName = sheetName + ran.nextInt(10);
            return getNewSheetName(wb, sheetName);
        } else
            return sheetName;
    }

    public static Integer getFileSheetNums(String pathString, String fileName)
            throws FileNotFoundException, Exception, IOException {
        Workbook wb = readExcelFile(pathString, fileName);
        if (wb == null) {
            logger.error("Workbook 对象为null, readExcelFile失败");
            return 0;
        }
        return wb.getNumberOfSheets();
    }

    //------------------------------------SHEET的ROW和COLUMN----------------------------------------------------//

    /**
     * 获取sheet的最大行数
     * 1Base, (用1base是便于for循环)
     * 注意sheet.getLastRowNum()是0Base,row.getLastCellNum()是1base
     */
    public static Integer getSheetRowsNum(Workbook wb, String sheetName) {
        if ((wb == null) || (StringUtils.isBlank(sheetName)))
            return -1;
        Sheet sheet1 = wb.getSheet(sheetName);
        return getSheetRowsNum(sheet1);
    }

    /**
     * 获取sheet的最大行数
     * 1Base, (用1base是便于for循环)
     * 注意sheet.getLastRowNum()是0Base,row.getLastCellNum()是1base
     */
    public static Integer getSheetRowsNum(Sheet sheet) {
        if (sheet == null)
            return -1;
        return sheet.getLastRowNum() + 1;
    }

    /**
     * 获取SHEET的最大列值 (遍历所有行,获取列数最大的行的列数作为SHEET的最大列数)
     * 1Base, (用1base是便于for循环)
     * 注意sheet.getLastRowNum()是0Base,row.getLastCellNum()是1base
     *
     * @param wb
     * @param sheetName
     * @return 判空错误时, 返回-1
     */
    public static Integer getSheetColumnsNum(Workbook wb, String sheetName) {
        if ((wb == null) || (StringUtils.isBlank(sheetName)))
            return -1;
        Sheet sheet1 = wb.getSheet(sheetName);
        return getSheetColumnsNum(sheet1);
    }

    /**
     * 获取SHEET的最大列值 (遍历所有行,获取列数最大的行的列数作为SHEET的最大列数)
     * 1Base, (用1base是便于for循环)
     * 注意sheet.getLastRowNum()是0Base,row.getLastCellNum()是1base
     *
     * @return 判空错误时, 返回-1
     */
    public static Integer getSheetColumnsNum(Sheet sheet) {
        if (sheet == null)
            return -1;

        int maxRows = getSheetRowsNum(sheet);
        int maxColumns = 0;
        // 遍历SHEET所有行,一直到sheet1.getLastRowNum(),取列数最大的行的列数为整个SHEET的最大列数
        for (int rowNum = 0; rowNum < maxRows; rowNum++) {
            Row aRow = sheet.getRow(rowNum);
            if (null != aRow) {  //遇到空行就跳过去,EXCEL存在一些空行
                int currentRowLastCell = aRow.getLastCellNum();
                if (currentRowLastCell > maxColumns)
                    maxColumns = currentRowLastCell;
            }
        }

        return maxColumns;
    }


    public static void resizeSheet(Sheet sheet, Integer newMaxRows, Integer newMaxCols) {
        for (int i = 0; i < newMaxRows; i++) {
            Row row = sheet.getRow(i);
            // 新行全新建
            if (row == null) {
                row = sheet.createRow(i);
                logger.debug("resize:建新行,NO." + i + "行,并自动建" + newMaxCols + "列");
                for (int j = 0; j < newMaxCols; j++) {
                    row.createCell(j);
                }
            }
            // 旧行,建新列
            else {
                for (int j = 0; j < newMaxCols; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        row.createCell(j);
                        logger.debug("resize:建新列,NO." + i + "行,NO." + j + "列");
                    }
                }
            }
        }
    }

    //------------------------------------CELL 的坐标-----------------------------------------------

    /**
     * 返回一个SHEET的 对应坐标的CELL
     *
     * @param sheet
     * @param x     ROW NUM
     * @param y     COLUMN NUM
     * @return 返回HSSFCell对象
     * @author guanxiaoyu
     */
    public static Cell getCell(Sheet sheet, int x, int y) {
        if ((sheet != null) && (x >= 0) && (y >= 0)) {
            Row row = sheet.getRow(x);
            if (row == null) {
                // 可能是空行,EXCEL存在一些空行,或行首的单元格没做过任何写修改,
                return null;
            } else {
                Cell cell = row.getCell(y);
                return cell;
            }
        } else
            return null;
    }

    /**
     * 返回一个SHEET的 对应EXCEL坐标(如"B5")的CELL
     *
     * @param sheet ROW NUM
     *              COLUMN NUM
     * @return 返回HSSFCell对象
     * @author guanxiaoyu
     */
    public static Cell getCell(Sheet sheet, String pos) {
        Point point = AxisFormulaTrans.getCellPoint(pos);
        return getCell(sheet, point.x, point.y);
    }

    /**
     * 返回一个CELL的 对应坐标(Point对象)
     *
     * @param cell
     * @return 返回Point对象
     * @author guanxiaoyu
     */
    public static Point getCellPoint(Cell cell) {
        if (cell != null)
            return new Point(cell.getRowIndex(), cell.getColumnIndex());
        else
            return null;
    }

    /**
     * 返回一个CELL的 对应format(如"B3")
     *
     * @param cell
     * @return 返回String(如"B3")
     * @author guanxiaoyu
     */
    public static String getCellFormatStr(Cell cell) {
        if (cell != null) {
            return AxisFormulaTrans.getCellPos(cell.getRowIndex(), cell.getColumnIndex());
        } else
            return null;
    }

    //-------------------------------CELL 值和公式-------------------------------------------------

    /**
     * 获取某个CELL值
     * 如果是公式,且传入了evaluator,就计算了FORMULA的值,如果没传入evaluator,就传回""
     * 日期型数,FORMAT作为参数输入
     * 浮点数,FORMAT和浮点数的为0表示都取缺省值
     *
     * @param aCell
     * @param evaluator  FORMULA计算需要evaluator
     * @param dateFormat 日期型数FORMAT,如"yyyy年MM月"
     * @return
     * @throws Exception
     */
    public static String getCellValue(Cell aCell, FormulaEvaluator evaluator
            , String dateFormat) throws Exception {

        if (aCell == null)
            return null;

        //logger.debug("类型:"+aCell.getCellType());

        StringBuilder sb = new StringBuilder();

        switch (aCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                sb.append(aCell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                // 数字包括日期型和DOUBLE型两种
                //日期型,转换为字符串,可以传入日期PASER,如果不传入,缺省用yyyy-MM-dd
                if (DateUtil.isCellDateFormatted(aCell)) {
                    sb.append(
                            BatchDateTimeUtil.format(
                                    aCell.getDateCellValue(), dateFormat));
                }
                //DOUBLE型,转换为字符串,可以传入PASER,这里不传入,缺省是#.##小数点2位,NULL值用0.00
                else {
                    sb.append(
                            DataDealUtil.doubleNumericDisp(
                                    aCell.getNumericCellValue(), null, null));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                sb.append(aCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                //logger.debug("计算公式:"+aCell.getCellFormula());
                // 计算公式,必须用仿真器
                if (evaluator != null) {
                    CellValue cellValue = null;
                    try {
                        cellValue = evaluator.evaluate(aCell);
                    } catch (Exception e) {
                        logger.error("仿真器转换失败:" + e.getMessage());
                        throw new Exception("仿真器转换失败:" + e.getMessage());
                    }
                    if (cellValue != null) {
                        //logger.debug("公式:"+aCell.getCellFormula()+"|公式结果值:"+DataDealUtil.doubleNumericDisp(cellValue.getNumberValue(),null,null)+"|cellValueType:"+cellValue.getCellType());
                        switch (cellValue.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                sb.append(cellValue.getStringValue());
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                double dValue = cellValue.getNumberValue();
                                // POI的公式numberic没有date处理,都认为是数字
                                sb.append(DataDealUtil.doubleNumericDisp(
                                        dValue, null, null));
                                break;

                            case Cell.CELL_TYPE_BOOLEAN:
                                sb.append(cellValue.getBooleanValue());
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                sb.append(cellValue.formatAsString());
                                break;
                            // CELL_TYPE_FORMULA will never happen
                            case Cell.CELL_TYPE_FORMULA:
                                break;
                        }// end of switch
                    } else //  if(cellValue!=null){
                        sb.append("");

                } else {
                    logger.debug("仿真器为空");
                    sb.append("");
                }
                break;
            default:
                sb.append("");
        }// end of switch

        return sb.toString();
    }


    /**
     * 确定某字符串的CELL TYPE
     *
     * @param value
     * @return
     */
    public static Integer getStringCellType(String value) {

        Integer cellType = Cell.CELL_TYPE_STRING;
        //确定CELL 类型
        if (StringUtils.isBlank(value)) {
            cellType = Cell.CELL_TYPE_BLANK;
        } else if (DataDealUtil.isNumber(value)) {
            cellType = Cell.CELL_TYPE_NUMERIC;
        } else {
            cellType = Cell.CELL_TYPE_STRING;
        }
        return cellType;
    }

    /**
     * 改格值,不支持公式,只改非公式的格
     *
     * @param cell
     * @param cellValue
     * @return
     * @throws Exception
     */
    public static Cell setCellValue(Cell cell, String cellValue, boolean trimFlag) throws Exception {
        // 是否先trim ?
        if (trimFlag)
            cellValue = cellValue.trim();

        Integer newCellType = 1;
        if (StringUtils.isBlank(cellValue))
            //newCellType = Cell.CELL_TYPE_BLANK;         // 设""时,POI会自动将类型由STRING改为BLANK
            newCellType = Cell.CELL_TYPE_STRING;
        else if (DataDealUtil.isNumber(cellValue))
            newCellType = Cell.CELL_TYPE_NUMERIC;
        else
            newCellType = Cell.CELL_TYPE_STRING;

        if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
            cell = changeCellType(cell, newCellType);
            cell = setCellValue(cell, cell.getCellType(), cellValue, null);
        }
        return cell;
    }


    /**
     * 用参数paramStr设CELL的cellValue
     * 如果CELL类型是公式,则用参数paramStr设公式,然后计算公式,将结果值设入cellValue
     * TODO... 没做清公式,即没做cell.setFormula("");
     *
     * @param aCell
     * @param cell_Type
     * @param paramStr
     * @param evaluator
     * @throws Exception
     */
    //TODO...  没有CELL_TYPE_NUMERIC类型的值判空的,目前是为空,就设为0.0
    public static Cell setCellValue(Cell aCell, Integer cell_Type, String paramStr, FormulaEvaluator evaluator) throws Exception {

        //logger.debug(paramStr+"|"+aCell.getRowIndex()+","+aCell.getColumnIndex()+"|"+cell_Type);

        // 1.如果设的类型与CELL原类型不同了,先改类型
        // 注意: 改类型后,CELL值都被改为0.00或"" ,所以要重新设值
        if (aCell.getCellType() != cell_Type) {
            changeCellType(aCell, cell_Type);
        }

        // 2. 如果类型不变,此处只是改值
        //    如果类型变了,那在改变的过程中,CELL值也被初始化了(变为""或0.00)
        //    总之都要重新设
        switch (cell_Type) {
            case Cell.CELL_TYPE_STRING:
                if (aCell.getSheet().getWorkbook() instanceof HSSFWorkbook) {
                    if (StringUtils.isBlank(paramStr) || paramStr.equals("null"))
                        paramStr = "";
                    aCell.setCellValue(new HSSFRichTextString(paramStr));
                } else {
                    if (StringUtils.isBlank(paramStr) || paramStr.equals("null"))
                        paramStr = "";
                    aCell.setCellValue(new XSSFRichTextString(paramStr));
                }

                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(aCell)) {
                    aCell.setCellValue(BatchDateTimeUtil.format(paramStr, "yyyy-MM-dd"));
                } else {
                    aCell.setCellValue(DataDealUtil.getDoubleFromStr(paramStr, false));
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                // 如果类型没变,说明原来就是BLANK,所以没有改值的必要
                // 类型变了,那在changeType()方法内,已经把cell值初始化成""了,不必改值
                // 关键是,aCell.setCellValue("");语句后,CELL类型会自动转为STRING,要重新setCellType(3),没这个必要
                break;
            case Cell.CELL_TYPE_FORMULA:
                if (StringUtils.isNotBlank(paramStr)) {
                    //logger.debug("格原值:"+ getCellValue(aCell, evaluator, null));
                    try {
                        aCell.setCellFormula(paramStr);// 此种情况,要求传入的第三个参数必须是formula
                    } catch (Exception e) {
                        logger.error("仿真器转换失败:" + e.getMessage());
                        throw new Exception("仿真器转换失败:" + e.getMessage());
                    }
                    //logger.debug("设置公式完毕!");
                    aCell = setCellValueByFormula(aCell, evaluator);
                    //logger.debug("格新值:"+ getCellValue(aCell, evaluator, null));
                } else {
                    logger.debug("CELL公式被置为空!");
                    // changeCellType()方法内,CELL类型改为BLANK,CELLVALUE改为"", 即当公式清空时, 整个CELL改为BLANK
                    aCell = changeCellType(aCell, Cell.CELL_TYPE_BLANK);
                }

                break;
            default:
                logger.debug(AxisFormulaTrans.getCellFormatStr(aCell) + ":" + cell_Type);
        }// end of switch

        //logger.debug("设完值!");
        return aCell;
    }

    /**
     * 计算公式,设CellValue
     *
     * @param cell
     * @param evaluator
     * @throws Exception
     */
    public static Cell setCellValueByFormula(Cell cell, FormulaEvaluator evaluator) throws Exception {
        //logger.debug("进入set value by formula:"+cell.getRowIndex()+","+cell.getColumnIndex());
        if (cell.getCellType() != Cell.CELL_TYPE_FORMULA)
            return cell;
        else {
            if (StringUtils.isNotBlank(getCellFormula(cell)) && evaluator != null) {
                //logger.debug("公式:"+cell.getCellFormula());
                CellValue cellValue = null;
                try {
                    cellValue = evaluator.evaluate(cell);
                } catch (Exception e) {
                    logger.error("仿真器转换失败:" + e.getMessage());
                    throw new Exception("仿真器转换失败:" + e.getMessage());
                }
                //logger.debug("cell value type:"+cellValue.getCellType());
                switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(cellValue.getStringValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double dValue = cellValue.getNumberValue();
                        // POI的公式numberic没有date处理,都认为是数字
                        cell.setCellValue(dValue);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(cellValue.getBooleanValue());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        cell.setCellValue("");
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    // CELL_TYPE_FORMULA will never happen
                    case Cell.CELL_TYPE_FORMULA:
                        break;
                }// end of switch

                return cell;

            } else if (StringUtils.isBlank(getCellFormula(cell))) {
                // CELL类型改为BLANK,CELLVALUE改为"", 即当公式清空时, 整个CELL改为BLANK
                cell = changeCellType(cell, Cell.CELL_TYPE_BLANK);
                return cell;
            }
        }

        return cell;

    }

    /**
     * 改类型都是先设值,后改类型,所以单独为了改数字类型,都会先把值改为0.00,在外部调用的时候,要记得把值改回来
     * <p/>
     * 改CELL类型为公式(2)时的注意事项:
     * 1.必须将值清空
     * 2.必须将CELL类型先改为BLANK(3)类型,作为跳板,再从3改为2, 不能直接从TEXT(1)改为公式(2)
     *
     * @param cell
     * @return
     * @throws Exception
     */
    public static Cell changeCellType(Cell cell, Integer typeValue) throws Exception {

        // 把CELL类型改为BLANK
        cell.setCellValue("");
        cell.setCellType(Cell.CELL_TYPE_BLANK);// cell.setCellValue("")语句后,类型会自动改为Cell.CELL_TYPE_STRING,
        //所以需要手工改为Cell.CELL_TYPE_BLANK

        if (typeValue != Cell.CELL_TYPE_BLANK) {
            try {
                //改数字前,先设数字值,
                if (typeValue == 0) {
                    cell.setCellValue(0.00d);//在外部要记得再通过 setCellValue设回来
                }
                //改字符串前,先设"",
                if (typeValue == 1) {
                    cell.setCellValue(""); //在外部要记得再通过 setCellValue设回来
                }
                cell.setCellType(typeValue);
            } catch (Exception e) {
                logger.debug("更改CELL类型时发生异常:" + e.getMessage());
                throw new Exception("更改CELL类型时发生异常:" + e.getMessage());
            }
        }
        /*logger.debug(cell.getSheet().getSheetName()+"工作簿下的"+
				AxisFormulaTrans.getCellPos(cell.getRowIndex(),cell.getColumnIndex())+
				"格的cell type设置为:"+cell.getCellType());*/
        return cell;
    }

    /**
     * 获取CELL FORMULA公式字串
     * 如果CELL不是公式,就返回其值(并且进行FORMAT), 或者,注释掉, 返回""
     *
     * @param aCell 日期型FORMAT,如"yyyy年MM月"
     * @return
     */
    public static String getCellFormula(Cell aCell) {
        if (aCell == null) {
            return "";
        }
        if (aCell.getCellType() == Cell.CELL_TYPE_FORMULA)
            return aCell.getCellFormula();
        else
            return "";
    }


    /**
     * 判断CELL是否是公式(CELL FORMULA)?
     *
     * @param aCell
     * @return true false
     * @author guanxiaoyu
     */
    public static boolean isCellFormula(Cell aCell) {

        if (aCell == null) {
            return false;
        }
        if (aCell.getCellType() == Cell.CELL_TYPE_FORMULA)
            return true;
        else
            return false;
    }

    /**
     * 遍历sheet内的cell,针对公式类型的CELL,重新计算它的公式,将值写入cellValue
     *
     * @param wbDispacher
     * @throws Exception
     * @throws IOException
     */
    public static void refreshSheetEvaluate(WorkbookDispacher wbDispacher) throws Exception, IOException {
        logger.debug("开始重新计算SHEET公式");
        Long currentTime = BatchDateTimeUtil.getCurentTimeLong();
        Workbook wb = wbDispacher.getWb();
        Integer sheetNums = wb.getNumberOfSheets();
        for (int i = 0; i < sheetNums; i++) {
            String sheetName = wb.getSheetName(i);
            Sheet sheet1 = wbDispacher.getWb().getSheet(sheetName);

            int maxRows = getSheetRowsNum(wbDispacher.getWb(), sheetName);
            int maxCols = getSheetColumnsNum(wbDispacher.getWb(), sheetName);

            FormulaEvaluator evaluator = wbDispacher.getCurrentEvaluator();

            //遍历sheet内的cell
            for (int rowNum = 0; rowNum < maxRows; rowNum++) {
                for (int colNum = 0; colNum < maxCols; colNum++) {
                    Cell cell = getCell(sheet1, rowNum, colNum);
                    // 针对公式类型的CELL,重新计算它的公式,将值写入cellValue
                    if (cell != null) {
                        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            cell = setCellValueByFormula(cell, evaluator);
                        } else {

                        }
                    } else {
                        logger.debug("有空格!(" + rowNum + "," + colNum + ")");
                    }

                }// end of for
            }// end of for
        }

        logger.debug("重新计算SHEET公式完成,time:" + BatchDateTimeUtil.getConsumeTime(currentTime));


    }


    public static void refreshSheetEvaluate(Sheet sheet1, FormulaEvaluator evaluator) throws Exception, IOException {

        int maxRows = getSheetRowsNum(sheet1);
        int maxCols = getSheetColumnsNum(sheet1);

        //遍历sheet内的cell
        for (int rowNum = 0; rowNum < maxRows; rowNum++) {
            for (int colNum = 0; colNum < maxCols; colNum++) {
                Cell cell = getCell(sheet1, rowNum, colNum);
                // 针对公式类型的CELL,重新计算它的公式,将值写入cellValue
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        cell = setCellValueByFormula(cell, evaluator);
                    }
                } else {
                    logger.debug("有空格!(" + rowNum + "," + colNum + ")");
                }

            }// end of for
        }// end of for

    }


    //------------------------------------CELL 合并单元格------------------------------------------------

    /**
     * 判断CELL是否属于某个合并单元格组
     *
     * @param sheet
     * @param cell
     * @return 在合并单元格组中, 且是首格, 返回2
     * 在合并单元格组中,但不是首格,返回1
     * 不在合并单元格组中,返回0
     * @author guanxiaoyu
     */
    public static int isMergedRegion(Sheet sheet, Cell cell) {

        //获得一个 sheet 中合并单元格组的总数
        int sheetmergerCount = sheet.getNumMergedRegions();

        // 遍历SHEET中的每个合并单元格组
        for (int i = 0; i < sheetmergerCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);

            //获得合并单元格组的起始行, 结束行, 起始列, 结束列
            int firstC = ca.getFirstColumn();
            int lastC = ca.getLastColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();

            //判断参数单元格是否是在当前合并单元格组中,
            //如果是, 则返回所在合并单元格的首单元格的值
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                    if ((cell.getRowIndex() == firstR) && (cell.getColumnIndex() == firstC))
                        return FIRSTMERGE; //在合并单元格中,且是首格
                    else
                        return INMERGE_BUT_NOFIRST;  //在合并单元格中,但不是首格
                } // end of if
            }  //end of if

        }// end of for

        return NONMERGE;   // 不在合并单元格中
    }

    /**
     * 判断CELL是否属于某个合并单元格组,如果是,返回该合并单元格组的对象
     *
     * @param sheet
     * @param cell
     * @return
     */
    public static CellRangeAddress getMergedRegion(Sheet sheet, Cell cell) {

        //获得一个 sheet 中合并单元格组的总数
        int sheetmergerCount = sheet.getNumMergedRegions();

        // 遍历SHEET中的每个合并单元格组
        for (int i = 0; i < sheetmergerCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            //获得合并单元格组的起始行, 结束行, 起始列, 结束列
            int firstC = ca.getFirstColumn();
            int lastC = ca.getLastColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();

            //判断参数单元格是否是在当前合并单元格组中,
            //如果是, 则返回所在合并单元格的首单元格的值
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                    return ca;
                } // end of if
            }  //end of if

        }// end of for

        return null;
    }


}
